import { App, Inject, Provide } from '@midwayjs/core';
import { BaseService } from '@cool-midway/core';
import { InjectDataSource, InjectEntityModel } from '@midwayjs/typeorm';
import { DataSource, Repository } from 'typeorm';
import { GoodsPromoMtEntity } from '../entity/promo_mt';
import { GoodsPromoMzEntity } from '../entity/promo_mz';
import { GoodsPromoTcEntity } from '../entity/promo_tc';
import { GoodsPromoMsEntity } from '../entity/promo_ms';
import { GoodsPromoMjEntity } from '../entity/promo_mj';
import * as oracledb from 'oracledb';
import { IMidwayApplication } from '@midwayjs/core';
import { ILogger } from '@midwayjs/logger';
import { OracleService } from '../../../comm/service/oracle.service';
import { TrimUtil } from '../../../comm/service/trim.util';

@Provide()
export class GoodsPromoSyncService extends BaseService {
  @InjectEntityModel(GoodsPromoMtEntity)
  promoMtEntity: Repository<GoodsPromoMtEntity>;

  @InjectEntityModel(GoodsPromoMzEntity)
  promoMzEntity: Repository<GoodsPromoMzEntity>;

  @InjectEntityModel(GoodsPromoTcEntity)
  promoTcEntity: Repository<GoodsPromoTcEntity>;

  @InjectEntityModel(GoodsPromoMsEntity)
  promoMsEntity: Repository<GoodsPromoMsEntity>;

  @InjectEntityModel(GoodsPromoMjEntity)
  promoMjEntity: Repository<GoodsPromoMjEntity>;

  @InjectDataSource()
  defaultDataSource: DataSource;

  @App()
  app: IMidwayApplication;

  @Inject()
  logger: ILogger;

  @Inject()
  oracleService: OracleService;

  /**
   * 执行 Oracle 存储过程 (促销相关)
   * @param connection Oracle 连接
   * @param procedureName 存储过程名称
   * @param bindParams 绑定参数 (包含 rfid)
   * @param taskId 任务 ID
   * @param logPrefix 日志前缀
   * @returns Promise<any[]> 结果数组
   */
  private async executePromoOracleProcedure(
    connection: oracledb.Connection,
    procedureName: string,
    bindParams: oracledb.BindParameters,
    taskId: string,
    logPrefix: string
  ): Promise<any[]> {
    this.logger.info(`[${taskId}] ${logPrefix} Calling SP ${procedureName}...`);
    const execOptions: oracledb.ExecuteOptions = {
      outFormat: oracledb.OUT_FORMAT_OBJECT,
      autoCommit: false
    };
    try {
      // 促销存储过程的参数名是 :rfid
      const result = await connection.execute<{ DATASET: oracledb.ResultSet<any> }>(
        `BEGIN ${procedureName}(:DATASET, :rfid); END;`,
        bindParams,
        execOptions
      );

      this.logger.debug(`[${taskId}] ${logPrefix} SP ${procedureName} raw result outBinds:`, result.outBinds);

      if (result && result.outBinds && result.outBinds.DATASET) {
        const resultSet = result.outBinds.DATASET;
        if (resultSet && typeof resultSet.getRows === 'function') {
          const rows = await resultSet.getRows();
          await resultSet.close();
          this.logger.info(`[${taskId}] ${logPrefix} Fetched ${rows.length} rows from Oracle SP ${procedureName}.`);
          return rows;
        } else {
          this.logger.warn(`[${taskId}] ${logPrefix} SP ${procedureName} result did not contain a valid ResultSet in outBinds.DATASET. Value:`, resultSet);
          return [];
        }
      } else {
        this.logger.warn(`[${taskId}] ${logPrefix} SP ${procedureName} did not return expected outBinds or outBinds.DATASET.`);
        return [];
      }
    } catch (spError) {
      this.logger.error(`[${taskId}] ${logPrefix} Error executing SP ${procedureName}:`, spError);
      return [];
    }
  }

  /**
   * 同步所有促销数据
   * @param taskId 任务 ID
   */
  async syncAllPromoData(taskId: string = 'manualSyncAllPromo') {
    this.logger.info(`[${taskId}] 开始执行促销数据同步任务...`);
    let totalPromosToSync = 0;
    let processedPromos = 0;
    let mtUpdated = 0, mtInserted = 0, mtDeleted = 0;
    let mzUpserted = 0, tcUpserted = 0, msUpserted = 0, mjUpserted = 0;
    let mzDeleted = 0, tcDeleted = 0, msDeleted = 0, mjDeleted = 0;

    const currentOracleDataSource = this.oracleService.getDataSource();
    if (!currentOracleDataSource || !currentOracleDataSource.isInitialized) {
      this.logger.error(`[${taskId}] Oracle 数据源 (via OracleService) 不可用或未初始化，任务中止。`);
      return;
    }

    let connection: oracledb.Connection | undefined;
    try {
      // 1. 获取 Oracle 连接
      this.logger.info(`[${taskId}] 获取 Oracle 连接 (从 DataSource 连接池)...`);
      const driver = currentOracleDataSource.driver as any;
      const pool = driver.pool || driver.master;
      if (!pool || typeof pool.getConnection !== 'function') {
        this.logger.error(`[${taskId}] 无法从 Oracle DataSource driver 获取连接池. Driver keys: ${Object.keys(driver).join(', ')}`);
        throw new Error('Could not obtain Oracle connection pool from TypeORM DataSource driver.');
      }
      connection = await pool.getConnection();
      this.logger.info(`[${taskId}] Oracle 连接获取成功.`);

      // 2. 查询需要同步的促销 ID 列表
      this.logger.info(`[${taskId}] 查询 YXC_B2B_INTERFACELIST 获取待同步促销ID...`);
      const promoQuery = `SELECT DISTINCT RFID FROM YXC_B2B_INTERFACELIST WHERE INTERFACETYPE = 'PROMO' AND RFID IS NOT NULL`;
      const promoResult = await connection.execute<{ RFID: string }>(promoQuery, [], { outFormat: oracledb.OUT_FORMAT_OBJECT });

      const promoIdsToSync = promoResult.rows?.map(row => row.RFID) || [];
      totalPromosToSync = promoIdsToSync.length;
      this.logger.info(`[${taskId}] 从 YXC_B2B_INTERFACELIST 获取到 ${totalPromosToSync} 个待同步的促销ID.`);

      // 获取本地促销汇总映射
      this.logger.info(`[${taskId}] 获取本地促销汇总映射...`);
      const localPromos = await this.promoMtEntity.find({ select: ['id', 'billNo', 'status'] });
      const localPromoMap = new Map(localPromos.map(p => [p.billNo, { id: p.id, status: p.status }]));
      const localPromoBillNos = Array.from(localPromoMap.keys());
      this.logger.info(`[${taskId}] 获取到 ${localPromoMap.size} 个本地促销汇总记录.`);

      // 3. 删除本地存在但Oracle中不存在的促销数据
      const promosToDelete = localPromoBillNos.filter(billNo => !promoIdsToSync.includes(billNo));
      if (promosToDelete.length > 0) {
        this.logger.info(`[${taskId}] 检测到 ${promosToDelete.length} 个本地促销需要删除: ${promosToDelete.join(', ')}`);
        
        // 删除所有子表数据
        for (const billNo of promosToDelete) {
          const mzDeleteResult = await this.promoMzEntity.delete({ billNo });
          const tcDeleteResult = await this.promoTcEntity.delete({ billNo });
          const msDeleteResult = await this.promoMsEntity.delete({ billNo });
          const mjDeleteResult = await this.promoMjEntity.delete({ billNo });
          
          mzDeleted += mzDeleteResult.affected || 0;
          tcDeleted += tcDeleteResult.affected || 0;
          msDeleted += msDeleteResult.affected || 0;
          mjDeleted += mjDeleteResult.affected || 0;
          
          this.logger.debug(`[${taskId}] 已删除促销 ${billNo} 的所有子表数据`);
        }
        
        // 删除主表数据
        if (promoIdsToSync.length > 0) {
          // 如果有要同步的数据，则精确删除不在同步列表中的数据
          const deleteResult = await this.promoMtEntity
            .createQueryBuilder()
            .delete()
            .where('billNo NOT IN (:...promoIds)', { promoIds: promoIdsToSync })
            .execute();
          mtDeleted = deleteResult.affected || 0;
        } else {
          // 如果没有要同步的数据，删除所有数据
          const deleteResult = await this.promoMtEntity.delete({});
          mtDeleted = deleteResult.affected || 0;
        }
        
        this.logger.info(`[${taskId}] 清理完成 - 删除了 ${mtDeleted} 条主表记录`);
        
        // 更新本地映射
        for (const billNo of promosToDelete) {
          localPromoMap.delete(billNo);
        }
      }

      if (totalPromosToSync === 0) {
        this.logger.info(`[${taskId}] 没有需要同步的促销数据，仅执行清理操作.`);
        this.logger.info(`[${taskId}] 清理统计:`);
        this.logger.info(`    PromoMt: 删除 ${mtDeleted} 条`);
        this.logger.info(`    PromoMz: 删除 ${mzDeleted} 条`);
        this.logger.info(`    PromoTc: 删除 ${tcDeleted} 条`);
        this.logger.info(`    PromoMs: 删除 ${msDeleted} 条`);
        this.logger.info(`    PromoMj: 删除 ${mjDeleted} 条`);
        return;
      }

      // 4. 遍历促销 ID，同步数据
      for (const promoId of promoIdsToSync) {
        processedPromos++;
        const logPrefix = `Promo ${promoId} (${processedPromos}/${totalPromosToSync}):`;
        this.logger.info(`[${taskId}] ${logPrefix} 开始处理...`);
        let promoMtProcessedSuccessfully = false;
        let currentLocalPromoId: number | undefined = undefined;

        const existingLocalData = localPromoMap.get(promoId);
        if (existingLocalData) {
          currentLocalPromoId = existingLocalData.id;
        }

        try {
          // --- 同步 PromoMt (促销汇总信息) ---
          const promoMtParams: oracledb.BindParameters = {
            DATASET: { type: oracledb.CURSOR, dir: oracledb.BIND_OUT },
            rfid: { type: oracledb.STRING, dir: oracledb.BIND_IN, val: promoId }
          };
          const oraclePromoMts = await this.executePromoOracleProcedure(connection, 'YXC_B2B_PROMOMT', promoMtParams, taskId, `${logPrefix} PromoMt`);

          if (!oraclePromoMts || oraclePromoMts.length === 0) {
            this.logger.warn(`[${taskId}] ${logPrefix} SP YXC_B2B_PROMOMT 未返回促销汇总数据. 跳过此促销.`);
            continue;
          }

          const oraclePromoMt = oraclePromoMts[0];

          // 准备促销汇总数据
          const rawPromoMtData: Partial<GoodsPromoMtEntity> = {
            billNo: promoId,
            billCode: oraclePromoMt.BILLCODE,
            entId: oraclePromoMt.ENTID,
            dates: oraclePromoMt.DATES,
            onTime: oraclePromoMt.ONTIME,
            promName: oraclePromoMt.PROMNAME,
            billState: oraclePromoMt.BILLSTATE || 0,
            ruleId: oraclePromoMt.RULEID,
            caozy: oraclePromoMt.CAOZY,
            promCon: oraclePromoMt.PROMCON,
            remark: oraclePromoMt.REMARK,
            promType: oraclePromoMt.PROMTYPE || 'A',
            startTime: oraclePromoMt.STARTTIME,
            endTime: oraclePromoMt.ENDTIME,
            status: 1
          };
          
          // 使用TrimUtil处理字符串字段，排除日期时间字段
          const promoMtData = TrimUtil.trimObjectFields(rawPromoMtData, ['dates', 'startTime', 'endTime']);

          // 执行更新或插入促销汇总
          if (currentLocalPromoId) {
            await this.promoMtEntity.update(currentLocalPromoId, promoMtData);
            mtUpdated++;
            promoMtProcessedSuccessfully = true;
            this.logger.debug(`[${taskId}] ${logPrefix} PromoMt 更新 (ID: ${currentLocalPromoId}).`);
          } else {
            const newPromoMt = this.promoMtEntity.create(promoMtData as GoodsPromoMtEntity);
            await this.promoMtEntity.save(newPromoMt);
            currentLocalPromoId = newPromoMt.id;
            localPromoMap.set(promoId, { id: currentLocalPromoId, status: 1 });
            mtInserted++;
            promoMtProcessedSuccessfully = true;
            this.logger.debug(`[${taskId}] ${logPrefix} PromoMt 新增 (New ID: ${currentLocalPromoId}).`);
          }

          // 仅当 PromoMt 处理成功时，才同步明细数据
          if (promoMtProcessedSuccessfully) {
            // --- 同步 PromoMz (买赠明细) ---
            await this.syncPromoDetail(connection, promoId, 'YXC_B2B_PROMOMZ', this.promoMzEntity, taskId, logPrefix, 'Mz');
            mzUpserted++;

            // --- 同步 PromoTc (套餐明细) ---
            await this.syncPromoDetail(connection, promoId, 'YXC_B2B_PROMOTC', this.promoTcEntity, taskId, logPrefix, 'Tc');
            tcUpserted++;

            // --- 同步 PromoMs (秒杀明细) ---
            await this.syncPromoDetail(connection, promoId, 'YXC_B2B_PROMOMS', this.promoMsEntity, taskId, logPrefix, 'Ms');
            msUpserted++;

            // --- 同步 PromoMj (满减明细) ---
            await this.syncPromoDetail(connection, promoId, 'YXC_B2B_PROMOMJ', this.promoMjEntity, taskId, logPrefix, 'Mj');
            mjUpserted++;
          }

          this.logger.info(`[${taskId}] ${logPrefix} 处理完成.`);

        } catch (promoError) {
          this.logger.error(`[${taskId}] ${logPrefix} 处理失败:`, promoError);
        }
      }

      this.logger.info(`[${taskId}] 促销数据同步任务完成.`);
      this.logger.info(`    总计待同步促销: ${totalPromosToSync}`);
      this.logger.info(`    成功处理促销: ${processedPromos}`);
      this.logger.info(`    PromoMt: 更新 ${mtUpdated} 条, 新增 ${mtInserted} 条, 删除 ${mtDeleted} 条`);
      this.logger.info(`    PromoMz: Upsert ${mzUpserted} 次, 删除 ${mzDeleted} 条`);
      this.logger.info(`    PromoTc: Upsert ${tcUpserted} 次, 删除 ${tcDeleted} 条`);
      this.logger.info(`    PromoMs: Upsert ${msUpserted} 次, 删除 ${msDeleted} 条`);
      this.logger.info(`    PromoMj: Upsert ${mjUpserted} 次, 删除 ${mjDeleted} 条`);

    } catch (error) {
      this.logger.error(`[${taskId}] 促销数据同步任务执行期间发生严重错误:`, error);
    } finally {
      // 释放连接
      if (connection) {
        this.logger.info(`[${taskId}] 释放 Oracle 连接回连接池...`);
        try {
          await connection.close();
          this.logger.info(`[${taskId}] Oracle 连接已释放.`);
        } catch (releaseError) {
          this.logger.error(`[${taskId}] 释放 Oracle 连接时出错:`, releaseError);
        }
      }
    }
  }

  /**
   * 同步促销明细数据的通用方法
   */
  private async syncPromoDetail(
    connection: oracledb.Connection,
    promoId: string,
    procedureName: string,
    entity: Repository<any>,
    taskId: string,
    logPrefix: string,
    detailType: string
  ) {
    const detailParams: oracledb.BindParameters = {
      DATASET: { type: oracledb.CURSOR, dir: oracledb.BIND_OUT },
      rfid: { type: oracledb.STRING, dir: oracledb.BIND_IN, val: promoId }
    };
    const oracleDetails = await this.executePromoOracleProcedure(connection, procedureName, detailParams, taskId, `${logPrefix} ${detailType}`);

    // 先删除旧明细
    this.logger.debug(`[${taskId}] ${logPrefix} Deleting existing ${detailType} details for promoId: ${promoId}`);
    await entity.delete({ billNo: promoId });

    if (oracleDetails && oracleDetails.length > 0) {
      const detailsToSave = oracleDetails.map(detail => {
        const baseData = {
          billNo: promoId,
          billSn: detail.BILLSN || 0,
          entId: detail.ENTID,
          status: 1
        };

        let specificData = {};
        // 根据不同类型添加特定字段
        switch (detailType) {
          case 'Mz': // 买赠
            specificData = {
              goodsId: detail.GOODSID,
              num: detail.NUM || 0,
              freeId: detail.FREEID,
              freeNum: detail.FREENUM || 0,
              freeTaxPrice: detail.FREETAXPRICE || 0,
              taxprice: detail.TAXPRICE || 0
            };
            break;
          case 'Tc': // 套餐
            specificData = {
              goodsId: detail.GOODSID,
              num: detail.NUM || 0,
              taxprice: detail.TAXPRICE || 0
            };
            break;
          case 'Ms': // 秒杀
            specificData = {
              goodsId: detail.GOODSID,
              seckillNum: detail.SECKILLNUM || 0,
              seckillPrice: detail.SECKILLPRICE || 0,
              seckillStorNum: detail.SECKILLSTORNUM || 0
            };
            break;
          case 'Mj': // 满减
            specificData = {
              taxAmount: detail.TAXAMOUNT || 0,
              dedRate: detail.DEDRATE || 0
            };
            break;
        }

        const rawData = { ...baseData, ...specificData };
        // 使用TrimUtil处理字符串字段，排除数值字段
        const numericFields = ['billSn', 'status', 'num', 'freeNum', 'freeTaxPrice', 'taxprice', 
                              'seckillNum', 'seckillPrice', 'seckillStorNum', 'taxAmount', 'dedRate'];
        const trimmedData = TrimUtil.trimObjectFields(rawData, numericFields);
        
        return entity.create(trimmedData);
      });

      if (detailsToSave.length > 0) {
        await entity.save(detailsToSave, { chunk: 50 });
        this.logger.debug(`[${taskId}] ${logPrefix} ${detailType} 保存了 ${detailsToSave.length} 条明细记录.`);
      }
    } else {
      this.logger.info(`[${taskId}] ${logPrefix} SP ${procedureName} 未返回${detailType}明细数据.`);
    }
  }
} 